
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Properties;
import java.util.Random;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Patrick
 */
public class TravelerManager {

    private final static int TRAVERLER_FLAG = 100;
    private final static String SQL_PUT_TRAVELER = "INSERT INTO cromb (author, message, longitude,latitude,flag) (SELECT ?,?,round(longitude,3) + (rand()/1000),round(latitude,3) + (rand()/1000),100 from cities order by RAND() LIMIT 5000)";

    private static void removeAllTravelers(Connection con) throws SQLException {
        Statement stat = con.createStatement();
        stat.execute("DELETE FROM cromb WHERE flag=" + TRAVERLER_FLAG);
        stat.close();

    }

    private static void addTraveler(Connection con, String author, String message) throws SQLException {
        PreparedStatement stat = con.prepareStatement(SQL_PUT_TRAVELER);
        stat.setString(1, author);
        stat.setString(2, message);
        stat.execute();
        stat.close();
    }

    public static void moveTravelers(Connection con) throws IOException, SQLException {
        Properties list = loadTravelers();
        removeAllTravelers(con);
        Enumeration keys = list.keys();
        while (keys.hasMoreElements()) {
            String key = keys.nextElement().toString();
            String line = list.getProperty(key);
            String author = line.split("@")[0];
            String message = line.split("@")[1];
            generateByRandomCity(con, author, message);
        }
    }

    private static Properties loadTravelers() throws IOException {
        Properties list = new Properties();
        list.load(TravelerManager.class.getResourceAsStream("Travelers.properties"));
        return list;
    }

    public static Connection getConnection(String host, String db, String user, String pass) throws SQLException {
        String url = "jdbc:mysql://" + host + "/" + db;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(CrombManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return DriverManager.getConnection(url, user, pass);
    }

    private static int generateByRandomCity(Connection con, String author, String message) throws SQLException {
        int id = 0;
        int total = 0;
        Statement stat = con.createStatement();
        ResultSet rs = stat.executeQuery("SELECT count(*) as c from cities");
        while (rs.next()) {
            total = rs.getInt("c");
        }
        rs.close();
        if (total > 0) {
            int i = new Random().nextInt(total);
            rs = stat.executeQuery("SELECT longitude,latitude from cities LIMIT " + i + ",1");
            double lon = 0;
            double lat = 0;
            while (rs.next()) {
                lon = rs.getDouble("longitude");
                lat = rs.getDouble("latitude");
            }
            rs.close();
            addTraveler(con,author, message);
        }
        stat.close();
        return id;
    }

    public static void main(String[] args) {

        Connection con = null;
        try {
            con = getConnection("mysql1001.mochahost.com:3306", "pballeux_crombz", "pballeux_Cromb", "garett16120");
            moveTravelers(con);
        } catch (SQLException ex) {
            Logger.getLogger(CrombManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(TravelerManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(CrombManager.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

    }
}
